package com.example.prom.ok.y2023.work.maxmind;

import com.alibaba.fastjson.JSON;
import com.example.prom.listener.ExcelListener;
import com.example.prom.strategy.excel.CustomCellStyleStrategy;
import com.example.prom.strategy.excel.CustomColumnWidthStrategy;
import com.maxmind.geoip2.DatabaseReader;
import com.maxmind.geoip2.model.CityResponse;
import com.maxmind.geoip2.record.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.net.InetAddress;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

import static com.alibaba.excel.EasyExcelFactory.read;
import static com.alibaba.excel.EasyExcelFactory.write;

@Slf4j
public class MaxMindHandler {

    public static void main(String[] args) throws Exception{
        List<MaxMindIpReaderVO> maxMindIpReaderVOS = readExcel();
        List<RegionVO2> regionVO2s = readExcel2();
        List<String> regions = regionVO2s.stream().map(RegionVO2::getRegion).collect(Collectors.toList());
//        System.out.println(maxMindIpReaders.size());
        List<MaxMindIpReaderVO> notMatchList = new ArrayList<>();
        List<MaxMindIpReaderVO> matchList = new ArrayList<>();
        for (MaxMindIpReaderVO maxMindIpReaderVO : maxMindIpReaderVOS) {
//            log.info("maxMindIpReader:{}",JSON.toJSONString(maxMindIpReaderVO));
            String num = maxMindIpReaderVO.getNum();
            String[] split = maxMindIpReaderVO.getApplicable_ip_addresses()
                    .replace("'","").replace("[","")
                    .replace("]","").split(",");
            boolean matchFlag = true;
            List<String> reasons = new ArrayList<>();
            List<String> remarks = new ArrayList<>();
            for (String s : split) {
                MaxMindResultDTO maxMindResultDTO = validateIP(s.trim());
//                log.info("ip:{}, maxMindResultDTO:{}",s.trim(),JSON.toJSONString(maxMindResultDTO));
                String regionNameEn = maxMindResultDTO.getRegionNameEn();
                String countryNameEn = maxMindResultDTO.getCountryNameEn();
                String applicable_regions = maxMindIpReaderVO.getApplicable_regions();

                if (StringUtils.isEmpty(regionNameEn) || !applicable_regions.contains(regionNameEn)){
//                    log.info("检测出不匹配的记录，num:{}, applicable_regions:{},regionNameEn:{}", num, applicable_regions, regionNameEn);
                    matchFlag = false;
                    regionNameEn = StringUtils.isEmpty(regionNameEn) ? "not found region" : regionNameEn;
                    String reason = s.trim()+":"+regionNameEn;
                    reasons.add(reason);
                    if (regions.contains(countryNameEn) || regions.contains(regionNameEn)){
                        String remark = "ip：" + s + "对应的禁止国是：" + countryNameEn;
                        remarks.add(remark);
                    }
                }
            }

            if (matchFlag){
                maxMindIpReaderVO.setMatch_result("match");
                maxMindIpReaderVO.setReason("no reason");
                matchList.add(maxMindIpReaderVO);
            } else {
                notMatchList.add(maxMindIpReaderVO);
                maxMindIpReaderVO.setMatch_result("not match");
                maxMindIpReaderVO.setReason(JSON.toJSONString(reasons));
                if (!CollectionUtils.isEmpty(remarks)){
                    maxMindIpReaderVO.setRemark(JSON.toJSONString(remarks));
                    System.out.println("maxmind检查出与目标禁止国不符的禁止国："+JSON.toJSONString(maxMindIpReaderVO));
                }
            }

        }
        writeExcel(matchList);
        writeExcel2(notMatchList);
    }
//    public static void main(String[] args) {
////        MaxMindResultDTO maxMindResultDTO = validateIP("91.207.27.76");
////        System.out.println(JSON.toJSONString(maxMindResultDTO));
//        List<MaxMindIpReader> maxMindIpReaders = readExcel();
//        for (MaxMindIpReader maxMindIpReader : maxMindIpReaders) {
//            String applicable_regions = maxMindIpReader.getApplicable_regions();
//            String[] split = applicable_regions
//                    .replace("'","").replace("[","")
//                    .replace("]","").split(",");
//            if (split.length > 1){
//                log.info("maxMindIpReader.applicable_regions.length > 1 :{}", JSON.toJSONString(maxMindIpReader));
//            }
//
//        }
//    }


    public static List<MaxMindIpReaderVO> readExcel() {
        String fileName = "C:\\Users\\hp\\Desktop\\for_IP_mapping_sanctioned_country_user_list_03272023.xlsx";
        ExcelListener<MaxMindIpReaderVO> excelListener = new ExcelListener<>();
        read(fileName, MaxMindIpReaderVO.class, excelListener).sheet().doRead();
        List<MaxMindIpReaderVO> dataList = excelListener.getDataList();
        return dataList;
    }

    public static List<RegionVO2> readExcel2() {
        String fileName = "C:\\Users\\hp\\Desktop\\bannedCountry.xlsx";
        ExcelListener<RegionVO2> excelListener = new ExcelListener<>();
        read(fileName, RegionVO2.class, excelListener).sheet().doRead();
        List<RegionVO2> dataList = excelListener.getDataList();
        return dataList;
    }

    public static void writeExcel(List<MaxMindIpReaderVO> writerList){
        String writeFileName = "C:\\Users\\hp\\Desktop\\ok\\maxmind_ipip_match_20230614_2.xlsx";
        write(writeFileName, MaxMindIpReaderVO.class).sheet().head(MaxMindIpReaderVO.class)
                .registerWriteHandler(new CustomColumnWidthStrategy())
                .registerWriteHandler(new CustomCellStyleStrategy()).doWrite(writerList);
    }

    public static void writeExcel2(List<MaxMindIpReaderVO> writerList){
        String writeFileName = "C:\\Users\\hp\\Desktop\\ok\\maxmind_ipip_notMatch_20230614_2.xlsx";
        write(writeFileName, MaxMindIpReaderVO.class).sheet().head(MaxMindIpReaderVO.class)
                .registerWriteHandler(new CustomColumnWidthStrategy())
                .registerWriteHandler(new CustomCellStyleStrategy()).doWrite(writerList);
    }

    public static MaxMindResultDTO validateIP(String ip) {
        MaxMindResultDTO resultDTO = new MaxMindResultDTO();
        try {
            // A File object pointing to your GeoIP2 or GeoLite2 database
            File database = new File("C:\\Users\\hp\\Desktop\\ok\\work\\GeoIP2-City_20230613\\GeoIP2-City.mmdb");

// This creates the DatabaseReader object. To improve performance, reuse
// the object across lookups. The object is thread-safe.
            DatabaseReader reader = new DatabaseReader.Builder(database).build();

            InetAddress ipAddress = InetAddress.getByName(ip);

// Replace "city" with the appropriate method for your database, e.g.,
// "country".
            CityResponse response = reader.city(ipAddress);

            Country country = response.getCountry();
//        System.out.println(country.getIsoCode());            // 'US'
//        System.out.println(country.getName());               // 'United States'
//        System.out.println(country.getNames().get("zh-CN")); // '美国'

            Subdivision subdivision = response.getMostSpecificSubdivision();
//        System.out.println(subdivision.getName());    // 'Minnesota'
//        System.out.println(subdivision.getNames().get("zh-CN"));    // 'Minnesota'
//        System.out.println(subdivision.getIsoCode()); // 'MN'

            City city = response.getCity();
//        System.out.println(city.getName()); // 'Minneapolis'
//        System.out.println(city.getNames().get("zh-CN")); // 'Minneapolis'

            Postal postal = response.getPostal();
//        System.out.println(postal.getCode()); // '55455'

            Location location = response.getLocation();
//        System.out.println(location.getLatitude());  // 44.9733
//        System.out.println(location.getLongitude()); // -93.2323


            resultDTO.setCountryIsoCode(country.getIsoCode());
            resultDTO.setCountryNameEn(country.getName());
            resultDTO.setCountryNameZh(country.getNames().get("zh-CN"));
            resultDTO.setRegionIsoCode(subdivision.getIsoCode());
            resultDTO.setRegionNameEn(subdivision.getName());
            resultDTO.setRegionNameZh(subdivision.getNames().get("zh-CN"));
            resultDTO.setCityNameEn(city.getName());
            resultDTO.setCityNameZh(city.getNames().get("zh-CN"));
            resultDTO.setLatitude(location.getLatitude());
            resultDTO.setLongitude(location.getLongitude());

        } catch (Exception e){
            log.error("ip解析异常,ip:{}", ip, e);
            resultDTO.setRegionNameEn(e.getMessage());
            resultDTO.setRegionNameZh(e.getMessage());
        }
        return resultDTO;
    }
}
